package com.pur.zsms.common.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.PersistenceException;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import com.pur.zsms.common.exception.ExceptionFactory;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;

/**
 * <p>抽象基类，被实际的JPA DAO继承。</p>
 * <p>该类是JpaDaoSupport的子类，在父类的基础上提供了直接可使用的简单数据操作方法，
 * 并提供更强的分页查询支持（自动计算total count）。</p>
 * <p>Create Time: 2009-5-11           </p>
 * @author chenlin
 * <p>Update Time:    2009-6-24         </p>
 * <p>Updater:     mahx                 </p>
 * <p>Update Comments:  优化            </p>
 */
public abstract class GenericDao extends JpaDaoSupport {
	private final Log log = LogFactory.getLog(GenericDao.class);

	/**	
	 * <p>将新创建的entity纳入EntityManager的管理，并马上刷新到数据库中。</p>
	 * @param entity
	 * @throws GenericException
	 */
	@Transactional
	public void persist(final Object entity) throws GenericException {
		try {
			getTemplate().persist(entity);
			getTemplate().flush();
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>是在实体Bean 已经脱离了EntityManager 的管理时使用，当容器决定flush 时，数据将会同步到数据库中,
	 * 执行em.merge(Object obj)方法时，容器的工作规则:</p>
	 * <p>如果此时容器中已经存在一个受容器管理的具有相同ID 的Object实例，容器将会把参数obj的内容拷贝进这个受管理的实例，
	 * merge()方法返回受管理的实例，但参数obj仍然是分离的不受管理的。容器在决定Flush时把实例同步到数据库中</p>
	 * <p>如果容器中不存在具有相同ID 的Object实例。容器根据传进的obj参数Copy 出一个受容器管理的Object实例，
	 * 同时merge()方法会返回出这个受管理的实例，但参数obj仍然是分离的不受管理的。容器在决定Flush时把实例同步到数据库中。</p>
	 * <P>如果传递进merge ()方法的参数不是实体Bean，会引发一个IllegalArgumentException</p>
	 * <p>Create Time: 2009-5-11   </p>
	 * @param entity
	 * @throws GenericException
	 */
	public Object  merge(final Object entity) throws GenericException {
		try {
			Object obj  =  getTemplate().merge(entity);
			getTemplate().flush();
			return obj;
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>删除对象 </p>
	 * @param entity
	 * @throws GenericException
	 */
	public void remove(final Object entity) throws GenericException {
		try {
			getTemplate().remove(entity);
			getTemplate().flush();
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>返回实体对象            </p>
	 * <p>若实体Bean不存在,则返回null </p>
	 * <p>Create Time: 2009-5-11   </p>
	 * @param entity 
	 * @param id
	 * @return Object entity bean的实例
	 * @throws GenericException		若不是Entity Bean的话,都会引发IllegalArgumentException
	 */
	public <T> T find(final Class<T> entity, final Serializable id)
			throws GenericException {
		try {
			return getTemplate().find(entity, id);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>返回实体对象             </p>
	 * <p>若实体Bean不存在,则抛出javax.persistence.EntityNotFoundException,不保证实体Bean已被初始化</p>
	 * @param entity
	 * @param id
	 * @return Object
	 * @throws GenericException
	 */
	public <T> T getReference(final Class<T> entity, final Serializable id)
			throws GenericException {
		try {
			return getTemplate().getReference(entity, id);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>根据数据库中的状态刷新实体Bean,未提交的修改将被覆盖。</p>
	 * @param entity
	 * @throws PersistenceException
	 */
	public void refresh(Object entity) throws GenericException {
		try {
			getTemplate().refresh(entity);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/** 
	 * <p>检测实体当前是否被管理中</p>
	 * <p>该方法使用一个实体作为参数，如果这个实体对象当前正被持久化内容管理，返回值为true，
	 * 否则为false</p>
	 * @param entity
	 * @throws PersistenceException
	 */
	public boolean contains(Object entity) throws GenericException {
		try {
			return getTemplate().contains(entity);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/** 
	 * <p>分离所有当前正在被管理的实体</p>
	 * <p>在处理大量实体的时候，如果你不把已经处理过的实体从EntityManager 中分离出来，将会消耗你大量的内存。
	 * 调用EntityManager 的clear()方法后，所有正在被管理的实体将会从持久化内容中分离出来。
	 * 调用clear()方法之前先调用flush()方法保存更改，否则之前对实体所作的没提交任何改变将会掉失</p>
	 * @throws PersistenceException
	 */
	public void clear() {
		getTemplate().clear();
	}

	/**	
	 * <p>执行更新sql:Jpl更新语句</p>
	 * @param updateString
	 * @param values
	 * @throws GenericException
	 */
	public int executeUpdate(String updateString, Object... values)
			throws GenericException {
		try {
			return getTemplate().executeUpdate(updateString, values);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p> 执行更新sql:Jpl更新语句             </p>
	 * @param  updateString
	 * @throws GenericException
	 */
	public int executeUpdate(String updateString) throws GenericException {
		try {
			return getTemplate().executeUpdate(updateString);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}
	
	/**	
	 * <p>执行更新sql:原生SQL更新语句</p>
	 * @param updateString
	 * @param values
	 * @throws GenericException
	 */
	public int executeNativeUpdate(String updateString, Object... values)
			throws GenericException {
		try {
			return getTemplate().executeNativeUpdate(updateString, values);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p> 执行更新sql:原生SQL更新语句             </p>
	 * @param  updateString
	 * @throws GenericException
	 */
	public int executeNativeUpdate(String updateString) throws GenericException {
		try {
			return getTemplate().executeNativeUpdate(updateString);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/** 
	 * <p>原始sql查询，支持分页</p>
	 * @param sqlString
	 * @param pageController
	 * @return PaginationSupport
	 * @throws PersistenceException
	 */
	public PaginationSupport findByNativeQuery(String sqlString,
			final PageController pageController) throws GenericException {
		try {
			return findByNativeQuery(sqlString, null, pageController,
					(Object[]) null);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/** 
	 * <p>原始sql查询，支持分页</p>
	 * @param sqlString
	 * @param pageController
	 * @param values
	 * @return PaginationSupport
	 * @throws PersistenceException
	 */
	public PaginationSupport findByNativeQuery(String sqlString,
			final PageController pageController, Object... values)
			throws GenericException {
		try {
			return findByNativeQuery(sqlString, null, pageController, values);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/**	
	 * <p>原始sql查询，支持分页</p>
	 * @param sqlString
	 * @param entityClass 实体对象 如果不是实体对象，会抛出系统异常
	 * @param pageController
	 * @param values
	 * @return PaginationSupport
	 * @throws PersistenceException
	 */
	public PaginationSupport findByNativeQuery(String sqlString,
			Class entityClass, final PageController pageController,
			Object... values) throws GenericException {
		try {
			Assert.hasText(sqlString);
			// Count查询
			String countQueryString = " select count(1) as cnt from ("
					+ removeOrders(sqlString) + ") as yuxin";//加入别名 支持mysql 
			log.debug("countQueryString is /n {}."+countQueryString);
			log.debug("removeOrders(sqlString) is /n {}."+
					removeOrders(sqlString));
			int totalCount = 0;
			/**CreateNativeQuery在Hsqldb内存数据库与Oracle数据库中返回的Object类型不一致,Oracle返回BigDecimal类型,Hsqldb返回Integer类型*/
			Object object = createNativeQuery(countQueryString,
						null, values).getSingleResult();
			if (object == null) { 
				totalCount = 0;
			} else if (object instanceof BigDecimal) {
				totalCount = ((BigDecimal)object).intValue();
			} else if (object instanceof Integer) {
				totalCount = ((Integer)object).intValue();
			}else if(object instanceof BigInteger){
				totalCount = ((BigInteger)object).intValue();//
			}
			log.debug("totalCount:{}"+totalCount);
			if (totalCount < 1)
				return new PaginationSupport(null, 0, pageController);
			// 实际查询返回分页对象
			List list = getTemplate().findByNativeQuery(sqlString, entityClass, pageController, values);
//			Query query = createNativeQuery(sqlString, entityClass, values);
//			query.setFirstResult(pageController.getStartIndex()).setMaxResults(
//					pageController.getPageSize());
//			List list = query.getResultList();
			log.debug("list is {}." + list);
			return new PaginationSupport(list, totalCount, pageController);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	/** 
	 * <p>去除sql的orderby 子句，用于pagedQuery.</p>
	 * @param hql
	 * @return String
	 */
	private static String removeOrders(String hql) {
		Assert.hasText(hql);
		Pattern p = Pattern.compile("order\\s*by\\s*[[\\w|\\W]&&[^\\s]]*\\s*(asc|desc){0,1}\\s*$",
				Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	private Query createNativeQuery(String sql, Class entityClass,
			Object... values) {
		Assert.hasText(sql);
		Query query = null;
		if (entityClass == null) {
			query = getEntityManager().createNativeQuery(sql);
		} else {
			query = getEntityManager().createNativeQuery(sql, entityClass);
		}
		if (values != null) {
		    for (int i = 0; i < values.length; i++) {
			    query.setParameter(i+1, values[i]);
		    }
		}
		return query;
	}

	  /**	
	   * <p>去除sql的select 子句，未考虑union的情况</p>
	   * @param hql
	   * @return String
	   */
	  private String removeSelect(String hql) {
	    Assert.hasText(hql);
	    int beginPos = hql.toLowerCase().indexOf("from");
	    Assert.isTrue(beginPos != -1, " hql : " + hql
	        + " must has a keyword 'from'");
	    return hql.substring(beginPos);
	  }
	  
	/** 
	 * <p>Description: jpql分页查询</p>
	 * @param jpql
	 * @param pageController
	 * @param values
	 * @return PaginationSupport
	 * @throws PersistenceException
	 */
	public PaginationSupport findByQuery(String jpql,
			final PageController pageController, Object... values)
			throws GenericException {
		try {
			Assert.hasText(jpql);
			// Count查询
			String countQueryString = " select count(*) "
					+ removeSelect(removeOrders(jpql)) ;
			
			log.debug("countQueryString is /n {}."+ countQueryString);
			int totalCount = ((Long) createQuery(countQueryString, values)
					.getSingleResult()).intValue();
			log.debug("totalCount1:{}"+ totalCount);
			if (totalCount < 1)
				return new PaginationSupport(null, 0, pageController);

			// 实际查询返回分页对象
			log.debug("totalCount1:{}"+ totalCount);
			List list = getTemplate().find(jpql, pageController, values);
//			Query query = createQuery(jpql, values);
//			query.setFirstResult(pageController.getStartIndex()).setMaxResults(
//					pageController.getPageSize());
//			List list = query.getResultList();
			log.debug("list is {}."+ list);
			return new PaginationSupport(list, totalCount, pageController);
		} catch (Exception e) {
			throw ExceptionFactory.parse(e);
		}
	}

	private Query createQuery(String sql, Object... values) {
		Assert.hasText(sql);
		Query query = getEntityManager().createQuery(sql);
		for (int i = 0; i < values.length; i++) {
			query.setParameter(i+1, values[i]);
		}
		return query;
	}

	public static void main(String[] args) {
		System.out.println("select a from b order by a.c ->"
				+ GenericDao.removeOrders("select a from b order by a.c, a.d  desc"));
		System.out
				.println("select a from b order by a.c union select e from d order by d.f->"
						+ GenericDao
								.removeOrders("select a from b order by a.c union select e from d order by d.f "));

	}
}
